﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC0101BO
    /// </summary>
    public class VYC0101BO : DBConnectionBO
    {
        public VYC0101BO():base()
        {
            //
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong ModelVYC0101
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC0101> GetAllList()
        {
            var list = new List<ModelVYC0101>();
            const string query = "SELECT [Id],[IdTour],[NgayGioDi],[NgayGioDen],[GiaTour],[NgayCapNhat],[TrangThai],[IdUser]FROM [VYC-01-01]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC0101();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdTour = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.NgayGioDi = (dataTable.Rows[i][2] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][2];
                model.NgayCapNhat = (dataTable.Rows[i][16] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][16];
                model.TrangThai = (dataTable.Rows[i][17] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][17].ToString());
                model.IdUser = (dataTable.Rows[i][18] == DBNull.Value) ? string.Empty : dataTable.Rows[i][18].ToString();

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC0101 model)
        {
            var query = "INSERT INTO [VYC-01-01]([IdTour],[NgayGioDi],[NgayGioDen],[GiaTour],[NgayCapNhat],[TrangThai],[IdUser]) VALUES (@idtour, @ngaygiodi, @ngaygioden, @giatour, GETDATE(), @trangthai, @iduser)";
            var sqlCommand = new SqlCommand(query, connection);

            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = model.IdTour;
            sqlCommand.Parameters.Add("@ngaygiodi", SqlDbType.DateTime).Value = model.NgayGioDi;
            sqlCommand.Parameters.Add("@ngaygioden", SqlDbType.DateTime).Value = model.NgayGioDen;
            sqlCommand.Parameters.Add("@giatour", SqlDbType.Decimal).Value = model.GiaTour;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@iduser", SqlDbType.VarChar).Value = model.IdUser;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC0101 model)
        {
            var query = "UPDATE [VYC-01-01] SET " +
                        "IdTour = @idtour ,NgayGioDi = @ngaygiodi,NgayGioDen = @ngaygioden, GiaTour = @giatour,NgayCapNhat = GETDATE(), TrangThai = @trangthai WHERE Id = @id ";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = model.IdTour;
            sqlCommand.Parameters.Add("@ngaygiodi", SqlDbType.DateTime).Value = model.NgayGioDi;
            sqlCommand.Parameters.Add("@ngaygioden", SqlDbType.DateTime).Value = model.NgayGioDen;
            sqlCommand.Parameters.Add("@giatour", SqlDbType.Decimal).Value = model.GiaTour;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC0101 model)
        {
            const string query = "DELETE FROM [VYC-01-01] WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }


        public List<ModelVYC0101> GetByIdTour(string idtour, int trangthai)
        {
            var list = new List<ModelVYC0101>();
            try
            {
                var query = "SELECT * FROM [VYC-01-01] WHERE IdTour like @idtour and TrangThai = @trangthai Order By NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = idtour;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC0101();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdTour = reader["Id"].ToString();
                    model.NgayGioDi = (DateTime) reader["NgayGioDi"];
                    model.NgayGioDen = (DateTime)reader["NgayGioDen"];
                    model.GiaTour = Convert.ToDecimal(reader["GiaTour"]);
                    model.IdUser = reader["IdUser"].ToString();                    
                    model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }

    }
}